--    Author    : ChenErHao
--    Name      : DM.T_MTH_BAD_LOAN_BALANCE.HQL
--    Functions : 风险-不良贷款余额
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-22  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.T_MTH_BAD_LOAN_BALANCE PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 
SELECT
    nvl(T1.AREA_NO_ID,9999),
    T1.ORG_ID,
    nvl(T1.CUSTOMER_TYPE,9999),
    T1.PRODUCT_CATEGORY,
    nvl(T1.LOAN_PERIOD,9999),--贷款期限,
    T1.LPIC_SUB,--企业分行业,
    T1.LOANS_ACTUALLY,-- 贷款实际投向,
    T1.ENTERPRISE_SCALE,---企业规模,
    T1.ENTERPRISE_INVESTOR,--企业出资人经济成分,
    T1.GUARANTY_STYLE,--担保方式,
    T1.LOAN_STATUS,--贷款状态,
    T1.LOAN_QUALITY,--贷款质量,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_DATA_DATE#' then T1.LOAN_BALANCE end),0)  LOAN_BALANCE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_LAST_1M_END_DATE#' then T1.LOAN_BALANCE end),0)  LAST_MTH_LOAN_BALANCE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_LAST_12M_END_DATE#' then T1.LOAN_BALANCE end),0)  LAST_YEAR_LOAN_BALANCE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_DATA_DATE#' then T1.WEIGHT_AMT end),0)   WEIGHT_AMT
FROM
    (SELECT
            DATA_DATE              FISCAL_MTH_DIMNSN_ID,
            BELONG_AREA_CODE        AREA_NO_ID,--地区
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END   ORG_ID,--金融机构
            CUSTOMER_TYPE         CUSTOMER_TYPE,--客户类型
            CASE WHEN C.loan_pro_id_1 IS NULL THEN '9999'
                 WHEN C.loan_pro_id_1 ='' THEN '9999'
                 ELSE C.loan_pro_id_1 END PRODUCT_CATEGORY,--贷款产品类别
            LOANS_PERIOD           LOAN_PERIOD,--贷款期限,
            NVL(LPIC_SUB,'9999') LPIC_SUB,--企业分行业
            CASE WHEN G.loan_actually_id_1 IS NULL THEN '9999'
                 WHEN G.loan_actually_id_1 ='' THEN '9999'
                 ELSE G.loan_actually_id_1 END LOANS_ACTUALLY,--行业投向
            NVL(ENTERPRISE_SCALE,'CS05') ENTERPRISE_SCALE,--企业规模
            CASE WHEN E.investor_finicl_id_2 IS NULL THEN '9999'
                 WHEN E.investor_finicl_id_2 ='' THEN '9999'
                 ELSE E.investor_finicl_id_2 END  ENTERPRISE_INVESTOR,--企业出资人经济成分二级编码 
            CASE WHEN F.loan_insur_id_1 IS NULL THEN '9999'
                 WHEN F.loan_insur_id_1 ='' THEN '9999'
                 ELSE F.loan_insur_id_1 END   GUARANTY_STYLE,-- 贷款担保方式一级编码
            NVL(LOAN_STATUS ,'9999')           LOAN_STATUS,--贷款状态
            NVL(LOAN_QUALITY,'9999')           LOAN_QUALITY,--贷款质量
            SUM(LOAN_BALANCE)      LOAN_BALANCE,
            SUM(LOAN_OCCURRENCE_LEVEL)  WEIGHT_AMT
        FROM
            (SELECT BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB,LOANS_ACTUALLY,ENTERPRISE_INVESTOR,GUARANTY_STYLE,
            DATA_DATE,CUSTOMER_TYPE,LOANS_PERIOD,ENTERPRISE_SCALE,LOAN_STATUS,LOAN_QUALITY,LOAN_BALANCE,LOAN_OCCURRENCE_LEVEL
            FROM EDW.LOAB  
        WHERE
            DATA_DATE  IN ( '#V_DATA_DATE#' ,'#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#') AND LOAN_QUALITY IN ('FQ03','FQ04', 'FQ05'))A
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON A.DATA_ORG_CODE = B.ORG_ID  --金融机构
        LEFT JOIN DMCODE.t_loan_pro_type C ON A.PRODUCT_CATEGORY = C.LOAN_PRO_ID --贷款产品类别
        LEFT JOIN DMCODE.t_loans_actually G ON A.LOANS_ACTUALLY=G.loan_actually_id   --贷款行业投向
        LEFT JOIN DMCODE.t_investor_finicl_type_trans E ON A.ENTERPRISE_INVESTOR = E.investor_finicl_id --企业出资人经济成分
        LEFT JOIN DMCODE.t_loan_insur_type F ON A.GUARANTY_STYLE=F.loan_insur_id   -- 贷款担保方式
        GROUP BY
            DATA_DATE   , A.BELONG_AREA_CODE  ,  B.ORG_LVL_ID_2 ,
            CUSTOMER_TYPE  , C.loan_pro_id_1  ,  
            LOANS_PERIOD  ,  LPIC_SUB , G.loan_actually_id_1 ,
            ENTERPRISE_SCALE , E.investor_finicl_id_2  , F.loan_insur_id_1 ,
            LOAN_STATUS       ,
            LOAN_QUALITY) T1 
   GROUP BY 
 T1.AREA_NO_ID,
 T1.ORG_ID,
 T1.CUSTOMER_TYPE,
 T1.PRODUCT_CATEGORY,
 T1.LOAN_PERIOD,--贷款期限,
 T1.LPIC_SUB,--企业分行业,
 T1.LOANS_ACTUALLY,-- 贷款实际投向,
 T1.ENTERPRISE_SCALE,---企业规模,
 T1.ENTERPRISE_INVESTOR,--企业出资人经济成分,
 T1.GUARANTY_STYLE,--担保方式,
 T1.LOAN_STATUS,--贷款状态,
 T1.LOAN_QUALITY  
;